﻿Imports DTO
Imports System.Data.OleDb
Public Class CChiTietDiemMonHocDAO

    Public Shared Function TinhDiemTB() As List(Of CChiTietDiemMonHocCRT)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strsum = "select MaHocSinh,avg(DiemCuoiHocKy) as Diem from CHITIETDIEMMONHOC group by MaHocSinh"
        Dim _cmd As New OleDbCommand(_strsum, cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _danhsachdiem As New List(Of CChiTietDiemMonHocCRT)
        While _reader.Read()
            Dim _CTDMHcrt As New CChiTietDiemMonHocCRT()
            _CTDMHcrt.MaHocSinh = _reader(0)
            _CTDMHcrt.DiemTB = _reader(1)
            _danhsachdiem.Add(_CTDMHcrt)
        End While
        cnn.Close()
        Return _danhsachdiem
    End Function

    Public Function XemDuLieu(ByVal _DMHdto As CDiemMonHocDTO) As List(Of CChiTietDiemMonHocCRT)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _strtracuu = "select ctdmh.MaChiTietDiemMonHoc,ctdmh.MaHocSinh,ctdmh.Diem15,ctdmh.Diem1Tiet,ctdmh.DiemCuoiHocKy,ctdmh.MaDiemMonHoc,dmh.MaLop,dmh.MaMonHoc,dmh.TenHocKy,hs.HoTen from CHITIETDIEMMONHOC ctdmh,DIEMMONHOC dmh,HOCSINH hs where dmh.MaLop=" + _DMHdto.MaLop.ToString() + " and dmh.MaMonHoc=" + _DMHdto.MaMonHoc.ToString() + " and dmh.TenHocKy=" + _DMHdto.TenHocKy.ToString() + " and ctdmh.MaDiemMonHoc=dmh.MaDiemMonHoc and ctdmh.MaHocSinh=hs.MaHocSinh"
        Dim _cmd As New OleDbCommand(_strtracuu, cnn)
        Dim _reader As OleDbDataReader = _cmd.ExecuteReader()
        Dim _ds As New List(Of CChiTietDiemMonHocCRT)
        While _reader.Read()
            Dim _CTDMHcrt As New CChiTietDiemMonHocCRT()
            _CTDMHcrt.MaChiTietDiemMonHoc = _reader.GetInt32(0)
            _CTDMHcrt.MaHocSinh = _reader.GetInt32(1)
            _CTDMHcrt.Diem15 = _reader.GetFloat(2)
            _CTDMHcrt.Diem1Tiet = _reader.GetFloat(3)
            _CTDMHcrt.DiemCuoiHocKy = _reader.GetFloat(4)
            _CTDMHcrt.MaDiemMonHoc = _reader.GetInt32(5)
            _CTDMHcrt.MaLop = _reader.GetInt32(6)
            _CTDMHcrt.MaMonHoc = _reader.GetInt32(7)
            _CTDMHcrt.TenHocKy = _reader.GetInt32(8)
            _CTDMHcrt.HoTen = _reader.GetString(9)
            _ds.Add(_CTDMHcrt)
        End While
        cnn.Close()
        Return _ds
    End Function

    Public Sub ThemChiTietDiemMonHoc(ByVal _CTDMHdto As CChiTietDiemMonHocDTO)
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _str = "insert into CHITIETDIEMMONHOC(MaHocSinh,Diem15,Diem1Tiet,DiemCuoiHocKy,MaDiemMonHoc) values(" + _CTDMHdto.MaHocSinh.ToString() + "," + _CTDMHdto.Diem15.ToString() + "," + _CTDMHdto.Diem1Tiet.ToString() + "," + _CTDMHdto.DiemCuoiHocKy.ToString() + "," + _CTDMHdto.MaDiemMonHoc.ToString() + ")"
        Dim _cmd As New OleDbCommand(_str, cnn)
        _cmd.ExecuteNonQuery()
        cnn.Close()
    End Sub

    Public Function CapNhatChiTietDiemMonHoc(ByVal _dtt As DataTable) As Integer
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _str = "select MaChiTietDiemMonHoc,MaHocSinh,Diem15,Diem1Tiet,DiemCuoiHocKy,MaDiemMonHoc from CHITIETDIEMMONHOC"
        Dim _adapter As New OleDbDataAdapter(_str, cnn)
        Dim _builder As New OleDbCommandBuilder(_adapter)
        Dim _kt As Integer = _adapter.Update(_dtt)
        cnn.Close()
        Return _kt
    End Function

    Public Function LayDanhSachCTDMonHoc(ByVal _DMHdto As CDiemMonHocDTO) As DataTable
        Dim cnn As OleDbConnection = CDaTaProviderDAO.ConnectDB()
        cnn.Open()
        Dim _str = "select ctdmh.MaChiTietDiemMonHoc,ctdmh.MaHocSinh,ctdmh.Diem15,ctdmh.Diem1Tiet,ctdmh.DiemCuoiHocKy,ctdmh.MaDiemMonHoc from CHITIETDIEMMONHOC ctdmh,DIEMMONHOC dmh where dmh.MaLop=" + _DMHdto.MaLop.ToString() + " and dmh.MaMonHoc=" + _DMHdto.MaMonHoc.ToString() + " and dmh.TenHocKy=" + _DMHdto.TenHocKy.ToString() + " and ctdmh.MaDiemMonHoc=dmh.MaDiemMonHoc"
        Dim _adapter As New OleDbDataAdapter(_str, cnn)
        Dim _dtt As New DataTable()
        _adapter.Fill(_dtt)
        Return _dtt
        cnn.Close()
    End Function

End Class
